import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.KeyEvent;
import java.awt.event.KeyListener;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.swing.ImageIcon;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

import com.mysql.jdbc.PreparedStatement;

class AdminAttendForm extends JPanel{
	private static final long serialVersionUID = 1L;

	private  ImageIcon attH = new ImageIcon(this.getClass().getResource("images/header/attend.png"));
	JLabel atte;
	private attPane attMenu;

	public AdminAttendForm(){
		attMenu = new attPane();
		atte = new JLabel(attH);
		atte.setBounds(135, 35, attH.getIconWidth(), attH.getIconHeight());
		atte.setVisible(true);

		attMenu.setBounds(130, 65, 617, 437);

		add(attMenu);
		add(atte);

		setLayout(null);
		setOpaque(false);
		setSize(806,628);
		setVisible(true);


	}
	public  attPane getPane(){
		return attMenu;
	}
}

class attPane extends JPanel{
	private static final long serialVersionUID = 1L;
	private JLabel frlabel, tolabel;
	private JTextField userID;
	private JComboBox smonth, sday, syear, emonth, eday, eyear;
	private JTable tableMod;
	private DefaultTableModel attendList;
	String[] monthItems = { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"};
	String[] yearItems = { "2011"};
	int[] intDay = new int[31];
	String[] dayItems = new String[31];

	Connection conn = null;
	public attPane() {
		//construct components
		userID = new JTextField (5);
		frlabel = new JLabel ("from:");
		tolabel = new JLabel ("to:");
		smonth = new JComboBox(monthItems);
		sday = new JComboBox();
		syear = new JComboBox(yearItems);
		emonth = new JComboBox(monthItems);
		eday = new JComboBox();
		eyear = new JComboBox(yearItems);

		InitializeDrop(new Integer(31), "start");
		InitializeDrop(new Integer(31), "end");

		//construction of table

		String[] columnNames = {"Employee Name", "Time In", "Time Out", "Date", "Status", "Work Hours"};
		tableMod = new JTable();
		tableMod.setModel(new javax.swing.table.DefaultTableModel(null, columnNames){
			private static final long serialVersionUID = 1L;
			public boolean isCellEditable(int rowIndex, int columnIndex) {
				return false;
			}
		});
		tableMod.setFillsViewportHeight(true);
		tableMod.getTableHeader().setReorderingAllowed(false);
		attendList = (DefaultTableModel)tableMod.getModel();
		JScrollPane	scrollPane = new JScrollPane();
		scrollPane.setViewportView(tableMod);

		//adjust size and set layout
		setPreferredSize (new Dimension (617, 437));
		setLayout (null);
		setOpaque(false);
		setSize(617,437);
		setVisible(true);


		//add components
		add (userID);
		add (frlabel);
		add (tolabel);
		add (smonth);
		add (sday);
		add (syear);
		add (emonth);
		add (eday);
		add (eyear);

		add (scrollPane);

		//set component bounds (only needed by Absolute Positioning)
		userID.setBounds (30, 25, 255, 25);
		frlabel.setBounds (295, 25, 35, 25);
		smonth.setBounds(330, 25, 100, 25);
		sday.setBounds(430, 25, 50, 25);
		syear.setBounds(480, 25, 70, 25);
		tolabel.setBounds(295, 50, 20, 25);
		emonth.setBounds(330, 50, 100, 25);
		eday.setBounds(430, 50, 50, 25);
		eyear.setBounds(480, 50, 70, 25);
		scrollPane.setBounds(20, 85, 590, 550);


		userID.addKeyListener(new KeyListener(){

			public void keyTyped(KeyEvent e) {}
			public void keyPressed(KeyEvent e) {}
			public void keyReleased(KeyEvent e) {
				try{
					//userID.setText("");

					ConnectionFactory myFactory = ConnectionFactory.getFactory();
					Connection conn = myFactory.getConnection();

					Statement stat = conn.createStatement();

					String[] names = null;
					String uName = getUserName();
					String name1, name2, name3, name4, name5;
					ResultSet res = null;

					names = uName.split(" ",0);
					name1 = names[0];
					if (name1.contains(","))
						name1 = name1.split(",")[0];
					if(names.length==1)
						//search for both first name or last name
						res = stat.executeQuery("SELECT concat(firstName, ' ', middleInitial, ' ', lastName) as 'Full Name', timeIn as 'Time-In', timeOut as 'Time-Out', dateSal as 'Date', s.desc as 'Status', TIMEDIFF(timeOut,timeIn) as 'Work Hours'"+
								"from bernalcompany.attendance a, bernalcompany.employee e, bernalcompany.attendstatus s where a.dateSal >= '"+getSyear()+"-"+getSmonth()+"-"+getSday()+"'"+
					"and a.dateSal <= '"+getEyear()+"-"+getEmonth()+"-"+getEday()+"' and a.idemployee = e.idemployee and s.aStat = a.stat"+
								" and (firstName like '"+name1+"%' or lastName like'"+name1+"%')"); 
					else if (names.length==2)	{
						//search if (firstName lastName) or (lastName firstName) or (two firstNames) or (two lastNames) or (firstName middleInitial)
						name2 = names[1];
						if (name1.contains(","))
							name1 = name1.split(",")[0];
						res = stat.executeQuery("SELECT concat(firstName, ' ', middleInitial, ' ', lastName) as 'Full Name', timeIn as 'Time-In', timeOut as 'Time-Out', dateSal as 'Date', s.desc as 'Status', TIMEDIFF(timeOut,timeIn) as 'Work Hours'"+
								"from bernalcompany.attendance a, bernalcompany.employee e, bernalcompany.attendstatus s where a.dateSal >= '"+getSyear()+"-"+getSmonth()+"-"+getSday()+"'"+
					"and a.dateSal <= '"+getEyear()+"-"+getEmonth()+"-"+getEday()+"' and a.idemployee = e.idemployee and s.aStat = a.stat"+
								" and ((firstName like '"+name1+"%'and lastName like '"+name2+"%') or (firstName like '"+name1+"%' and middleInitial='"+name2+"')"+
								" or (lastName like '"+name1+"%'and firstName like '"+name2+"%') or (firstName like '"+name1.concat(" ").concat(name2)+"%')"+"or (lastName like '"+name1.concat(" ").concat(name2)+"%'))");

					} else if (names.length==3) { 
						//search if (firstName middleInitial lastName) or (lastName firstName middleInitial) or (firstName1 firstName2 middleInitial) or (firstname1 firstName2 lastName)or (firstName lastName1 lastName2) or (lastName1 lastName2 firstName) or (lastName firstname1 firstName2)
						name2 = names[1];
						name3 = names[2];
						if (name1.contains(","))
							name1 = name1.split(",")[0];
						else if (name2.contains(","))
							name2 = name2.split(",")[0];
						res = stat.executeQuery("SELECT concat(firstName, ' ', middleInitial, ' ', lastName) as 'Full Name', timeIn as 'Time-In', timeOut as 'Time-Out', dateSal as 'Date', s.desc as 'Status', TIMEDIFF(timeOut,timeIn) as 'Work Hours'"+
								"from bernalcompany.attendance a, bernalcompany.employee e, bernalcompany.attendstatus s where a.dateSal >= '"+getSyear()+"-"+getSmonth()+"-"+getSday()+"'"+
					"and a.dateSal <= '"+getEyear()+"-"+getEmonth()+"-"+getEday()+"' and a.idemployee = e.idemployee and s.aStat = a.stat"+
								" and ((firstName like '"+name1+"%' and middleInitial ='"+name2+"' and lastName like '"+name3+"%')"+
								" or (lastName like '"+name1+"%'and firstName like '"+name2+"%' and middleInitial='"+name3+"')"+
								" or (firstName like '"+name1.concat(" ").concat(name2)+"%' and middleInitial='"+name3+"') or (firstName like '"+name1.concat(" ").concat(name2)+"%' and lastName like '"+name3+"%') or (firstName like '"+name1+"%' and lastName like '"+name2.concat(" ").concat(name3)+"%')"+
								" or (lastName like '"+name1.concat(" ").concat(name2)+"%' and firstName like '"+name3+"%') or (lastName like '"+name1+"%' and firstName like '"+name2.concat(" ").concat(name3)+"%'))");
					} else if (names.length==4) { 
						//search if (firstName1 firstName2 middleInitial lastName) or (firstName middleInitial lastName1 lastName2) or (firstName1 firstName2 lastName1 lastName2) or (lastName1 lastName2 firstName1 firstName2)or (firstName lastName1 lastName2) or (lastName1 lastName2 firstName) or (lastName firstname1 firstName2)
						name2 = names[1];
						name3 = names[2];
						name4 = names[3];
						if (name1.contains(","))
							name1 = name1.split(",")[0];
						else if (name2.contains(","))
							name2 = name2.split(",")[0];
						res = stat.executeQuery("SELECT concat(firstName, ' ', middleInitial, ' ', lastName) as 'Full Name', timeIn as 'Time-In', timeOut as 'Time-Out', dateSal as 'Date', s.desc as 'Status', TIMEDIFF(timeOut,timeIn) as 'Work Hours'"+
								"from bernalcompany.attendance a, bernalcompany.employee e, bernalcompany.attendstatus s where a.dateSal >= '"+getSyear()+"-"+getSmonth()+"-"+getSday()+"'"+
					"and a.dateSal <= '"+getEyear()+"-"+getEmonth()+"-"+getEday()+"' and a.idemployee = e.idemployee and s.aStat = a.stat"+
								" and ((firstName like '"+name1.concat(" ").concat(name2)+"%' and middleInitial ='"+name3+"' and lastName like '"+name4+"%') or (firstName like '"+name1+"%' and middleInitial ='"+name2+"' and lastName like '"+name3.concat(" ").concat(name4)+"%')"+
								" or (firstName like '"+name1.concat(" ").concat(name2)+"%' and lastName like '"+name3.concat(" ").concat(name4)+"%') or (lastName like '"+name1.concat(" ").concat(name2)+"%' and firstName like '"+name3.concat(" ").concat(name4)+"%')"+
								" or (lastName like '"+name1.concat(" ").concat(name2)+"%' and firstName like '"+name3+"%' and middleInitial='"+name4+"') or (lastName like '"+name1+"%' and firstName like '"+name2.concat(" ").concat(name3)+"%' and middleInitial='"+name4+"'))");
					} else { 
						//search if (firstName1 firstName2 middleInitial lastName1 lastName2) or (lastName1 lastName2 firstName1 firstName2 middleInitial)
						name2 = names[1];
						name3 = names[2];
						name4 = names[3];
						name5 = names[4];
						if (name2.contains(","))
							name2 = name2.split(",")[0];
						res = stat.executeQuery("SELECT concat(firstName, ' ', middleInitial, ' ', lastName) as 'Full Name',timeIn as 'Time-In', timeOut as 'Time-Out', dateSal as 'Date', s.desc as 'Status', TIMEDIFF(timeOut,timeIn) as 'Work Hours'"+
								"from bernalcompany.attendance a, bernalcompany.employee e, bernalcompany.attendstatus s where a.dateSal >= '"+getSyear()+"-"+getSmonth()+"-"+getSday()+"'"+
					"and a.dateSal <= '"+getEyear()+"-"+getEmonth()+"-"+getEday()+"' and a.idemployee = e.idemployee and s.aStat = a.stat"+
								" and ((firstName like '"+name1.concat(" ").concat(name2)+"%' and middleInitial ='"+name3+"' and lastName like '"+name4.concat(" ").concat(name5)+"%')"+
								" or (lastName like '"+name1.concat(" ").concat(name2)+"%' and middleInitial ='"+name3+"' and firstName like '"+name4.concat(" ").concat(name5)+"%'))");
					}

					attendList.getDataVector().removeAllElements();
					tableMod.setAutoCreateRowSorter(true);

					while(res.next()){
						attendList.addRow(new Object[]{ res.getString("Full Name"), res.getTime("Time-In"), res.getTime("Time-Out"), res.getDate("Date"), res.getString("Status"), res.getInt("Work Hours")});
						attendList.fireTableDataChanged();
					}

					conn.close();




				}catch(Exception ex){
					ex.printStackTrace();
				}


			}

		});

		smonth.addActionListener(new ActionListener() {
			public void actionPerformed (ActionEvent e){

				boolean leap = false;

				leap = CheckLeap(Integer.parseInt(syear.getSelectedItem().toString()));

				if(smonth.getSelectedIndex() == 1)
				{
					if(leap==false)
						InitializeDrop(28, "start");
					else
						InitializeDrop(29, "start"); 
				}
				else if (smonth.getSelectedIndex() == 0 || smonth.getSelectedIndex() == 2 || smonth.getSelectedIndex() == 4 
						|| smonth.getSelectedIndex() == 6 || smonth.getSelectedIndex() == 7 || smonth.getSelectedIndex() == 9
						|| smonth.getSelectedIndex() == 11)
					InitializeDrop(31, "start");
				else if (smonth.getSelectedIndex() == 3 || smonth.getSelectedIndex() == 5 || smonth.getSelectedIndex() == 8
						|| smonth.getSelectedIndex() == 10)
					InitializeDrop(30, "start");
			}
		}
		);

		emonth.addActionListener(new ActionListener() {
			public void actionPerformed (ActionEvent e){

				boolean leap = false;

				leap = CheckLeap(Integer.parseInt(eyear.getSelectedItem().toString()));

				if(emonth.getSelectedIndex() == 1)
				{
					if(leap==false)
						InitializeDrop(28, "end");
					else
						InitializeDrop(29, "end"); 
				}
				else if (emonth.getSelectedIndex() == 0 || emonth.getSelectedIndex() == 2 || emonth.getSelectedIndex() == 4 
						|| emonth.getSelectedIndex() == 6 || emonth.getSelectedIndex() == 7 || emonth.getSelectedIndex() == 9
						|| emonth.getSelectedIndex() == 11)
					InitializeDrop(31, "end");
				else if (emonth.getSelectedIndex() == 3 || emonth.getSelectedIndex() == 5 || emonth.getSelectedIndex() == 8
						|| emonth.getSelectedIndex() == 10)
					InitializeDrop(30, "end");
			}
		}
		);

	}


	public void InitializeDrop(int ctr, String type) {
		if(type.equals("start")){
			sday.removeAllItems();

			for(int a = 1; a <= ctr; a++ )
			{     
				intDay[a-1] = a;
				dayItems[a-1] = Integer.toString(intDay[a-1]);
				sday.addItem(dayItems[a-1]);
			}
			sday.repaint();
		}
		else if(type.equals("end")){
			eday.removeAllItems();
			for(int a = 1; a <= ctr; a++ )
			{     
				intDay[a-1] = a;
				dayItems[a-1] = Integer.toString(intDay[a-1]);
				eday.addItem(dayItems[a-1]);
			}

			eday.repaint();

		}
	}


	public boolean CheckLeap(int year) {
		if(year%4 == 0)
		{
			if(year%100 != 0)
			{
				return true;
			}
			else
			{
				if(year%400 == 0)
					return true;
				else
					return false;
			}
		}
		return false;
	}

	public String getUserName(){
		String userName = userID.getText().trim();
		return userName;	    	
	}

	public int getCheckSpace(){

		String uName = userID.getText();

		char[] unameArray = uName.toCharArray();

		int i = 0;
		int lenSpace = uName.length();

		while ( lenSpace > 0)
		{ 	char c = unameArray[i];

		if ( c == ' '){
			//may space!
			return 1;
		}
		else
			lenSpace--;
		i++;
		}
		return 0;
	}
	public int getSmonth() {
		String mon = smonth.getSelectedItem().toString();
		int con = 0;
		if(mon.equals("January"))
			con = 1;
		else if(mon.equals("February"))
			con = 2;
		else if(mon.equals("March"))
			con = 3;
		else if(mon.equals("April"))
			con = 4;
		else if(mon.equals("May"))
			con = 5;
		else if(mon.equals("June"))
			con = 6;
		else if(mon.equals("July"))
			con = 7;
		else if(mon.equals("August"))
			con = 8;
		else if(mon.equals("September"))
			con = 9;
		else if(mon.equals("October"))
			con = 10;
		else if(mon.equals("November"))
			con = 11;
		else
			con = 12;

		return con;
	}

	public String getSday() {
		return sday.getSelectedItem().toString();
	}

	public String getSyear() {
		return syear.getSelectedItem().toString();
	}

	public int getEmonth() {
		String mon = emonth.getSelectedItem().toString();
		int con = 0;
		if(mon.equals("January"))
			con = 1;
		else if(mon.equals("February"))
			con = 2;
		else if(mon.equals("March"))
			con = 3;
		else if(mon.equals("April"))
			con = 4;
		else if(mon.equals("May"))
			con = 5;
		else if(mon.equals("June"))
			con = 6;
		else if(mon.equals("July"))
			con = 7;
		else if(mon.equals("August"))
			con = 8;
		else if(mon.equals("September"))
			con = 9;
		else if(mon.equals("October"))
			con = 10;
		else if(mon.equals("November"))
			con = 11;
		else
			con = 12;

		return con;
	}

	public String getEday() {
		return eday.getSelectedItem().toString();
	}

	public String getEyear() {
		return eyear.getSelectedItem().toString();
	}

	public void attendUpdate(){

		try
		{
			ConnectionFactory myFactory = ConnectionFactory.getFactory();
			Connection conn = myFactory.getConnection();
			PreparedStatement stat = (PreparedStatement) conn.prepareStatement("SELECT concat(firstName, ' ', middleInitial, ' ', lastName) as 'Full Name',"+
					"timeIn as 'Time-In', timeOut as 'Time-Out', dateSal as 'Date', s.desc as 'Status', TIMEDIFF(timeOut,timeIn) as 'Work Hours'"+
			"from bernalcompany.attendance a, bernalcompany.employee e, bernalcompany.attendstatus s where a.dateSal >= '"+getSyear()+"-"+getSmonth()+"-"+getSday()+"'"+
					"and a.dateSal <= '"+getEyear()+"-"+getEmonth()+"-"+getEday()+"' and a.idemployee = e.idemployee and s.aStat = a.stat"); 
			ResultSet res = stat.executeQuery();

			attendList.getDataVector().removeAllElements();
			tableMod.setAutoCreateRowSorter(true);

			while(res.next()){
				attendList.addRow(new Object[]{ res.getString("Full Name"), res.getTime("Time-In"), res.getTime("Time-Out"), res.getDate("Date"), res.getString("Status"), res.getInt("Work Hours")});
				attendList.fireTableDataChanged();
			}

			conn.close();

		}catch(Exception ex){
			ex.printStackTrace();
		}

	}


}